package com.lyy.common.utils.jxls;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.jxls.builder.xls.XlsCommentAreaBuilder;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;

import java.io.*;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class JxlsUtils {

    private Map<String,Integer> countMap = new HashMap<>(); //用于jxls的foreach循环计数

    static {
        //        图片加载
        XlsCommentAreaBuilder.addCommandMapping("image", ImageCommand.class);
//        合并单元格
        XlsCommentAreaBuilder.addCommandMapping("merge", MergeCommand.class);
    }

    public static void exportExcel(InputStream is, OutputStream os, Map<String, Object>  model) throws IOException {
        Context context = PoiTransformer.createInitialContext();
        if (model != null) {
            for (Map.Entry<String, Object> entry : model.entrySet()){
                context.putVar(entry.getKey(), entry.getValue());
            }
        }
        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        Transformer transformer  = jxlsHelper.createTransformer(is, os);
        //获得配置
        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
        //设置静默模式，不报警告
        evaluator.getJexlEngine().setSilent(true);
        //函数强制，自定义功能
        Map<String, Object> funcs = new HashMap<String, Object>();
		funcs.put("jx", new JxlsUtils());    //添加自定义功能
        evaluator.getJexlEngine().setFunctions(funcs);
        //必须要这个，否者表格函数统计会错乱
        jxlsHelper.setUseFastFormulaProcessor(false);
        CusJxlsHelper cusJxlsHelper = new CusJxlsHelper();
        cusJxlsHelper.processTemplate(context, transformer);
    }

    public static void exportExcel(File xls, File out, Map<String, Object> model) throws FileNotFoundException, IOException {
        exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
    }

    public static void exportExcel(String templatePath, OutputStream os, Map<String, Object> model) throws Exception {
        File template = getTemplate(templatePath);
        if(template != null){
            exportExcel(new FileInputStream(template), os, model);
        } else {
            throw new Exception("Excel 模板未找到。");
        }
    }

    //获取jxls模版文件
    public static File getTemplate(String path){
        File template = new File(path);
        if(template.exists()){
            return template;
        }
        return null;
    }


    //自定义一个计数器
    public  Integer count(String var){
        if (var == null) return null;
        if(countMap.containsKey(var)){
            Integer t = countMap.get(var);
            t += 1;
            countMap.replace(var,t);
            return t;
        }else{
            countMap.put(var,1);
        }
        return 1;
    }

    // 日期格式化
    public String dateFmt(Date date, String fmt) {
        if (date == null) {
            return "";
        }
        try {
            SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
            return dateFmt.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

    // if判断
    public Object ifelse(boolean b, Object o1, Object o2) {
        return b ? o1 : o2;
    }

//    身份证
    public String sfz(String sfz) {
        String back = sfz;
        if (back != null && back.length() != 0) {
            int length = sfz.length();
            int beforeLength = 4;
            int afterLength = 4;
            //替换字符串，当前使用“*”
            String replaceSymbol = "*";
            StringBuffer sb = new StringBuffer();
            for (int j = 0; j < length; j++) {
                if (j < beforeLength || j >= (length - afterLength)) {
                    sb.append(back.charAt(j));
                } else {
                    sb.append(replaceSymbol);
                }
            }
            back = sb.toString();
        }
        return back;
    }

//    人员性质
    public String workType(String workType){
        String type = "";
        if("0".equals(workType)){
            type="其他";
        }else if("1".equals(workType)){
            type="建设";
        }else if("2".equals(workType)){
            type="施工";
        }else if("3".equals(workType)){
            type="监理";
        }else if("4".equals(workType)){
            type="设计";
        }else if("5".equals(workType)){
            type="勘察";
        }
        return type;
    }
//    返回方式
    public String trafficTools(String trafficTools){
        String  tools = "";
        if("1".equals(trafficTools)){
            tools = "自驾或拼车";
        }else if("2".equals(trafficTools)){
            tools = "飞机";
        }else if("3".equals(trafficTools)){
            tools = "火车";
        }else if("4".equals(trafficTools)){
            tools = "船舶";
        }else if("5".equals(trafficTools)){
            tools = "长途汽车";
        }else{
            tools = "其他";
        }
        return tools;
    }

    public String percentage(float a,float b){
        NumberFormat nt = NumberFormat.getPercentInstance();
        //设置百分数精确度2即保留两位小数
        nt.setMinimumFractionDigits(0);
        float baifen = a/b;
        return nt.format(baifen);
    }

    /**
     * Excel 的分页名（页码）的封装
     * 此方法用来获取分好页的页名信息，将信息放入一个链表中返回
     */
    public static ArrayList<String> getSheetName(List<Page> page) {
        ArrayList<String> al = new ArrayList<String>();
        for (int i = 0; i < page.size(); i++) {
            al.add(page.get(i).getSheetName());
        }
        return al;
    }

    /**
     * 删除指定的Sheet 
     * @param targetFile  目标文件 
     * @param sheetName   Sheet名称 
     */
    public static void deleteSheet(String targetFile,String sheetName) {
        try {
            FileInputStream fis = new FileInputStream(targetFile);
            HSSFWorkbook wb = new HSSFWorkbook(fis);
            fileWrite(targetFile, wb);
            //删除Sheet 
            wb.removeSheetAt(wb.getSheetIndex(sheetName));
            fileWrite(targetFile, wb);
            fis.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 写隐藏/删除后的Excel文件
     * @param targetFile  目标文件
     * @param wb          Excel对象
     * @throws Exception
     */
    public static void fileWrite(String targetFile,HSSFWorkbook wb) throws Exception{
        FileOutputStream fileOut = new FileOutputStream(targetFile);
        wb.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }

}
